
package CSISL4pk;

import java.awt.*;
import java.sql.*;
/**
 *
 * @author Josh
 */
public class SQLSubmitNewCase {
    private ResultSet rs;
    private int vicID;
    private int suspID;
    private int witID;
    public SQLSubmitNewCase(){
        
    }
    public void submitIt(int caseNum, String caseDesc, String crimeType, String day,
            String month, String year, String locale, int userID, String street, String city,
            String state, String zip, String suspName, String vicName, String witName){
        //Load into 'cases' table
        try{ 
        
            String myDriver = "com.mysql.jdbc.Driver";
            String myUrl = "jdbc:mysql://www.db4free.net:3306/csisl";//www.freesql.org:3306/csisl";//"jdbc:mysql://SQL09.FREEMYSQL.NET:3306/csisl";

            Class.forName(myDriver);
            //Connection conn = DriverManager.getConnection(myUrl, "pablofig","cs2450");
            Connection conn = DriverManager.getConnection(myUrl, "csisaltlake","cs2450");
            // our SQL SELECT query. 
            // if you only need a few columns, specify them by name instead of using "*"
            String nCase = "UPDATE cases SET status = 'open',crimeType = '"+crimeType+"',"
                    + "locale = '"+locale+"', crimeDate = '"+year+"-"+month+"-"+day+"',"
                    + " notes = '"+caseDesc+"',lastUpdateTime = NOW(), inv_ID ="+userID+" WHERE caseNum = "+caseNum+" ";
            System.out.println(nCase);
            // create the java statement
            Statement st = conn.createStatement();

            // execute the query
            st.execute(nCase);
            //on finish always close connection
            st.close();
            
        }
        catch(Exception e){
             System.out.println(e);
             
        }
        //this loads into victimPerson
        try{ 
        
            String myDriver = "com.mysql.jdbc.Driver";
            String myUrl = "jdbc:mysql://www.db4free.net:3306/csisl";//www.freesql.org:3306/csisl";//"jdbc:mysql://SQL09.FREEMYSQL.NET:3306/csisl";

            Class.forName(myDriver);
            //Connection conn = DriverManager.getConnection(myUrl, "pablofig","cs2450");
            Connection conn = DriverManager.getConnection(myUrl, "csisaltlake","cs2450");
            // our SQL SELECT query. 
            // if you only need a few columns, specify them by name instead of using "*"
            String nCase = "INSERT INTO person (user_id, type, first) VALUES ("+userID+", 'victim', "
                    +"'"+vicName+"'),("+userID+", 'suspect', '"+suspName+"'),"
                    + "("+userID+", 'witness', '"+witName+"')";
            System.out.println(nCase);
            // create the java statement
            Statement st = conn.createStatement();

            // execute the query
            st.execute(nCase);
            //on finish always close connection
            st.close();
            
        }
        catch(Exception e){
             System.out.println(e);
             
        }
        //get people id
              try{ 
        
            String myDriver = "com.mysql.jdbc.Driver";
            String myUrl = "jdbc:mysql://www.db4free.net:3306/csisl";//www.freesql.org:3306/csisl";//"jdbc:mysql://SQL09.FREEMYSQL.NET:3306/csisl";

            Class.forName(myDriver);
            Connection conn2 = DriverManager.getConnection(myUrl, "csisaltlake","cs2450");
            String query = "SELECT id FROM person WHERE first ='"+vicName+"'and type = 'victim' or"
                    + " first ='"+suspName+"' and type = 'suspect' or first = '"+witName+"' and type = witness";
            Statement st2 = conn2.createStatement();
            
            rs = st2.executeQuery(query);
            int i = 1;
            while (rs.next())
                {
                    if( i == 1 ){
                        vicID = rs.getInt("id");}
                    else if( i == 2 )suspID = rs.getInt("id");
                    else if( i == 3) witID = rs.getInt("id");
                     i++;
                }
            st2.close();
               
        }
        catch(Exception e){
             System.out.println(e);
             
        }
        
        //Link people id to case number
        try{ 
        
            String myDriver = "com.mysql.jdbc.Driver";
            String myUrl = "jdbc:mysql://www.db4free.net:3306/csisl";//www.freesql.org:3306/csisl";//"jdbc:mysql://SQL09.FREEMYSQL.NET:3306/csisl";

            Class.forName(myDriver);
            //Connection conn = DriverManager.getConnection(myUrl, "pablofig","cs2450");
            Connection conn = DriverManager.getConnection(myUrl, "csisaltlake","cs2450");
            // our SQL SELECT query. 
            // if you only need a few columns, specify them by name instead of using "*"
            String nCase = "INSERT INTO case_to_person (case_id, person_id) VALUES ("+caseNum+", "+vicID
                    +"),("+caseNum+", "+suspID+"),"
                    + "("+caseNum+", "+witID+")";
            System.out.println(nCase);
            // create the java statement
            Statement st = conn.createStatement();

            // execute the query
            st.execute(nCase);
            //on finish always close connection
            st.close();
            
        }
        catch(Exception e){
             System.out.println(e);
             
        }
        
    }
}
